﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace QLNS_Server
{
    public static class sqlHelper
    {
        #region "FILL DATA TABLE"


        static string strConnect = "server=BPT02;database=QLNS;user id=sa; password=123456";
        //static string strConnect = "server=COMPUTER;database=QLNS;user id=sa; password=123456";
        //truy xuất data từ csdl và lưu vào dataTable
        public static void Fill(DataTable dataTable, String sqlCommandString)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataTable);

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

                }

            }

        }


        //truy xuất data từ csdl và lưu vào dataTable với câu lệnh sql có tham số truyền vào
        public static void Fill(DataTable dataTable, String sqlCommandString, SqlParameter[] parameters)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            if (parameters != null)

                oCommand.Parameters.AddRange(parameters);

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataTable);

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

                }

            }

        }



        #endregion



        #region "FILL DATASET"


        //truy xuất data từ csdl và lưu vào dataSet 
        public static void Fill(DataSet dataSet, String sqlCommandString)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataSet);

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

                }

            }

        }


        //truy xuất data từ csdl và lưu vào dataSet với câu lệnh sql có tham số truyền vào
        public static void Fill(DataSet dataSet, String sqlCommandString, SqlParameter[] parameters)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            if (parameters != null)

                oCommand.Parameters.AddRange(parameters);

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataSet);

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

                }

            }

        }



        #endregion



        #region "EXECUTE SCALAR"


        //thực thi lệnh sql trả về giá trị ở dòng đầu cột đầu của bảng cần truy xuất ở dạng object
        public static object ExecuteScalar(String sqlCommandString)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            object oReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    oCommand.Transaction = oTransaction;

                    oReturnValue = oCommand.ExecuteScalar();

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

                }

            }

            return oReturnValue;

        }


        //thực thi lệnh sql trả về giá trị ở dòng đầu cột đầu của bảng cần truy xuất ở dạng object với tham số được truyền vào khi thực thi 
        public static object ExecuteScalar(String sqlCommandString, SqlParameter[] parameters)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            object oReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    if (parameters != null)

                        oCommand.Parameters.AddRange(parameters);



                    oCommand.Transaction = oTransaction;

                    oReturnValue = oCommand.ExecuteScalar();

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

                }

            }

            return oReturnValue;

        }



        #endregion



        #region "EXECUTE NON QUERY"


        //Thực thi các lênh sql như: Insert, delete, update
        public static int ExecuteNonQuery(string sqlCommandString)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            int iReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    oCommand.Transaction = oTransaction;

                    iReturnValue = oCommand.ExecuteNonQuery();

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

                }

            }

            return iReturnValue;

        }


        //Thực thi các lênh sql như: Insert, delete, update có tham số truyền vào câu lệnh sql khi thực thi
        public static int ExecuteNonQuery(string sqlCommandString, SqlParameter[] parameters)
        {

            SqlConnection oConnection = new SqlConnection(strConnect);

            SqlCommand oCommand = new SqlCommand(sqlCommandString, oConnection);

            oCommand.CommandType = CommandType.Text;

            int iReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {

                try
                {

                    if (parameters != null)

                        oCommand.Parameters.AddRange(parameters);

                    oCommand.Transaction = oTransaction;

                    iReturnValue = oCommand.ExecuteNonQuery();

                    oTransaction.Commit();

                }

                catch
                {

                    oTransaction.Rollback();

                    throw;

                }

                finally
                {

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

                }

            }

            return iReturnValue;

        }



        #endregion
    }
}